from api.handler.ce.AbstractBatchCE import AbstractBatchCE
from api.model.BatchOutputPipe import BatchOutputPipe
from api.model.FdInputPipe import FdInputPipe
from munch import Munch
import json
# from config.annotation import Value
import datetime

now = datetime.datetime.now().year

def get_sex(id):
    if len(id) == 18:
        return '女' if int(id[-2])%2 == 0 else '男'
    else:
        return None

def get_birth(id):
    if len(id) == 18:
        return id[6:14]
    else:
        return None

def get_age(birth):
    if birth:
        age = now - int(birth[:4])
        if 10 < age <= 120:
            return age
        else:
            return None

def get_age_group(age):
    if age:
        return age // 10

class TeacherHomePageHandler(AbstractBatchCE):
    """ 本科当前状态 包含 当前在校，7内日疫情区 """
    def do_compute(self, output_stream: BatchOutputPipe, source_fds: FdInputPipe, params: dict):
        commu_fd = source_fds.get_fd('Community')

#         commu_sql = """
#         select CountyName,  count(Level) as Level from
# (SELECT distinct Level, CountyName FROM community where DataTime = DATE_SUB(curdate(),INTERVAL 1 DAY)) as a
# group by CountyName
#         """
        # 获取当天的危险区

        commu_sql = """
        select CountyName,  count(level) as level from
(select distinct level, CountyName from Community where datatime = DATE_SUB(curdate(),INTERVAL 1 DAY)) as a
group by CountyName 
        """
        commu_results = commu_fd.query(commu_sql)

        commu_list = []
        for commu in commu_results:
            commu_list.append(commu.CountyName)
        commu_tuple = tuple(commu_list)

        ss_fd = source_fds.get_fd('hr_user')
        ss_sql = """
        select UserName,UserActualName,UserIdentity,DepName from hr_user WHERE UserTypeId in (6, 7, 1, 5, 10019)
        """
        ss_results = ss_fd.query(ss_sql)

        spm_fd = source_fds.get_fd('scupersonmove')
        spm_sql = """
                SELECT DISTINCT PERSONNO
FROM scpersonmove
WHERE DATE > DATE_SUB(curdate(),INTERVAL 17 DAY) AND AREA in {} 
AND (STATUSNAME is null or STATUSNAME='教工')
                """.format(commu_tuple)
        spm_results = spm_fd.query(spm_sql)

        spm1_sql = """
       SELECT PERSONNO, LOCATE
FROM scupersonmove
WHERE DATE = DATE_SUB(curdate(),INTERVAL 1 DAY) AND (STATUSNAME is null or STATUSNAME='教工') AND LOCATE = 0
        """
        spm1_results = spm_fd.query(spm1_sql)
        # 当天 是否在校

        for result in ss_results:
            data = Munch()
            data.usercode = result.get('UserName')
            data.username = result.get('UserActualName')
            data.dept = result.get('DepName')
            userid = result.get('UserIdentity')
            data.userid = userid
            data.sex = get_sex(userid)
            data.birth = get_birth(userid)
            data.in_danger = 0
            data.in_school = 1
            data.age = get_age(data.birth)
            data.age_group = get_age_group(data.age)

            for spm in spm_results:
                if str(result.UserName) == str(spm.PERSONNO):
                    data.in_danger = 1
                    spm_results.remove(spm)
                    break
            for spm1 in spm1_results:
                if str(result.UserName) == str(spm1.PERSONNO):
                    data.in_school = 0
                    spm1_results.remove(spm1)
                    break

            output_stream.output_for_upsert(data)